package com.such.kit.database;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Map.Entry;
import javax.naming.NamingException;
import javax.sql.DataSource;
import org.apache.commons.collections4.multimap.ArrayListValuedHashMap;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jndi.JndiObjectFactoryBean;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.such.kit.Logger;
import com.such.kit.bean.BeanCommons;
import com.such.kit.database.bean.JdbcCallParam;
import com.such.kit.database.bean.JdbcQueryPackage;
import com.such.kit.database.bean.JdbcQueryParam;
import com.such.kit.database.bean.Page;
import com.such.kit.database.bean.SqlPackage;
import com.such.kit.database.dialect.DialectCommons;
import com.such.kit.database.support.JdbcResultSetMapping;
import com.such.kit.validate.ValidateCommons;

/**
 * @author SUCH
 * <pre>
 * JDBC 通用工具类
 * </pre>
 */
public class JdbcCommons {

	private JdbcTemplate jdbcTemplate;
	private MetaDataCommons metaDataCommons;
	private DialectCommons dialectCommons;

	/**
	 * <pre>
	 * 基于 jdbc 连接参数构建
	 * </pre>
	 * @param driver jdbc 驱动
	 * @param url jdbc URL
	 * @param username jdbc 用户
	 * @param password jdbc 密码
	 * @throws Exception
	 */
	public JdbcCommons(String driver, String url, String username, String password) 
			throws Exception {
		this(getDataSource(driver, url, username, password));
	}

	/**
	 * <pre>
	 * 基于 jndi 名称构建
	 * </pre>
	 * @param jndiName jndi 名称
	 * @throws Exception
	 */
	public JdbcCommons(String jndiName) 
			throws Exception {
		this(getDataSource(jndiName));
	}

	/**
	 * <pre>
	 * 基于数据源名称构建
	 * </pre>
	 * @param dataSource 数据源
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public JdbcCommons(DataSource dataSource) 
			throws SQLException, ClassNotFoundException {
		this(new JdbcTemplate(dataSource));
	}

	/**
	 * <pre>
	 * 基于 spring jdbc 模板构建
	 * </pre>
	 * @param jdbcTemplate spring jdbc 模板
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public JdbcCommons(JdbcTemplate jdbcTemplate) 
			throws SQLException, ClassNotFoundException {
		this.jdbcTemplate = jdbcTemplate;
		this.metaDataCommons = new MetaDataCommons(jdbcTemplate.getDataSource());
		this.dialectCommons = this.metaDataCommons.getDialectCommons();
	}

	/**
	 * <pre>
	 * 获取 spring jdbc 模板
	 * </pre>
	 * @return spring jdbc 模板
	 */
	public JdbcTemplate getJdbcTemplate() {
		return this.jdbcTemplate;
	}

	/**
	 * <pre>
	 * 获取数据库元信息工具
	 * </pre>
	 * @return 数据库元信息工具
	 */
	public MetaDataCommons getMetaDataCommons() {
		return this.metaDataCommons;
	}

	/**
	 * <pre>
	 * 获取方言处理工具
	 * </pre>
	 * @return 方言处理工具
	 */
	public DialectCommons getDialectCommons() {
		return this.dialectCommons;
	}

	/**
	 * <pre>
	 * 序列值（下一个）查询
	 * </pre>
	 * @param sequenceName 序列名称
	 * @return 序列值
	 */
	public long queryForSequenceNextValue(String sequenceName) {
		String sql = this.dialectCommons.SQL.buildDummyTableSql(this.dialectCommons.SQL.buildSequenceNextValueSql(sequenceName, null));
		return this.jdbcTemplate.queryForObject(sql, Long.class);
	}

	/**
	 * <pre>
	 * 调用存储过程、函数等
	 * </pre>
	 * @param callName call 的对象名
	 * @param args 参数列表
	 * @return 储过程、函数返回值
	 */
	public Map<Integer, Object> call(String callName, final List<JdbcCallParam> args) {
		List<String> params = new ArrayList<String>();
		if (ValidateCommons.isNotEmpty(args)) {
			for (int i = 0; i < args.size(); i++) {
				params.add("?");
			}
		}
		String callSql = this.dialectCommons.SQL.buildCallSql(callName, params);
		return this.jdbcTemplate.execute(callSql, new CallableStatementCallback<Map<Integer, Object>>() {

			@Override
			public Map<Integer, Object> doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
				List<Integer> outParamIndexs = new ArrayList<Integer>();
				for (int i = 0; i < args.size(); i++) {
					JdbcCallParam arg = args.get(i);
					String type = arg.getType();
					Object value = arg.getValue();
					if (JdbcCallParam.TYPE_IN.equals(type)) {
						cs.setObject(i + 1, value);
					} else if (JdbcCallParam.TYPE_OUT.equals(type)) {
						cs.registerOutParameter(i + 1, (Integer) value);
					}
					outParamIndexs.add(i);
				}
				Map<Integer, Object> results = new HashMap<Integer, Object>();
				if (cs.execute()) {
					for (int i : outParamIndexs) {
						Object value = null;
						JdbcCallParam arg = args.get(i);
						int sqlType = (Integer) arg.getValue();
						switch(sqlType) {
							case Types.BIT: 
								value = cs.getBoolean(i + 1);
								break;
							case Types.TINYINT: 
								value = cs.getByte(i + 1);
								break;
							case Types.SMALLINT: 
								value = cs.getInt(i + 1);
								break;
							case Types.INTEGER: 
								value = cs.getInt(i + 1);
								break;
							case Types.BIGINT: 
								value = cs.getLong(i + 1);
								break;
							case Types.FLOAT: 
								value = cs.getFloat(i + 1);
								break;
							case Types.REAL: 
								value = cs.getFloat(i + 1);
								break;
							case Types.DOUBLE: 
								value = cs.getDouble(i + 1);
								break;
							case Types.NUMERIC: 
								value = cs.getBigDecimal(i + 1);
								break;
							case Types.DECIMAL: 
								value = cs.getBigDecimal(i + 1);
								break;
							case Types.CHAR: 
								value = cs.getString(i + 1);
								break;
							case Types.VARCHAR: 
								value = cs.getString(i + 1);
								break;
							case Types.LONGVARCHAR: 
								value = cs.getString(i + 1);
								break;
							case Types.DATE: 
								value = cs.getDate(i + 1);
								break;
							case Types.TIME: 
								value = cs.getTime(i + 1);
								break;
							case Types.TIMESTAMP: 
								value = cs.getTimestamp(i + 1);
								break;
							case Types.BINARY: 
								value = cs.getBytes(i + 1);
								break;
							case Types.VARBINARY: 
								value = cs.getBytes(i + 1);
								break;
							case Types.LONGVARBINARY: 
								value = cs.getBytes(i + 1);
								break;
							case Types.OTHER: 
								value = cs.getObject(i + 1);
								break;
							case Types.JAVA_OBJECT: 
								value = cs.getObject(i + 1);
								break;
							case Types.ARRAY: 
								value = cs.getArray(i + 1);
								break;
							case Types.BLOB: 
								value = cs.getBytes(i + 1);
								break;
							case Types.CLOB: 
								value = cs.getString(i + 1);
								break;
							case Types.REF: 
								value = cs.getRef(i + 1);
								break;
							case Types.BOOLEAN: 
								value = cs.getBoolean(i + 1);
								break;
							case Types.NCHAR: 
								value = cs.getString(i + 1);
								break;
							case Types.NVARCHAR: 
								value = cs.getString(i + 1);
								break;
							case Types.LONGNVARCHAR: 
								value = cs.getString(i + 1);
								break;
							case Types.NCLOB: 
								value = cs.getString(i + 1);
								break;
							case Types.SQLXML: 
								value = cs.getSQLXML(i + 1);
								break;
						}
						results.put(i, value);
					}
				}
				return results;
			}
		});
	}

	/**
	 * <pre>
	 * 分页查询
	 * </pre>
	 * @param sql
	 * @param args 绑定参数，可以为空
	 * @param pageIndex 页数
	 * @param pageLimit 每页数据条数
	 * @return 分页对象
	 */
	public Page<Map<String, Object>> queryForPage(String sql, List<Object> args, int pageIndex, int pageLimit) {
		return queryForPage(sql, args, pageIndex, pageLimit, true);
	}

	/**
	 * <pre>
	 * 分页查询
	 * </pre>
	 * @param sql
	 * @param args 绑定参数，可以为空
	 * @param pageIndex 页数
	 * @param pageLimit 每页数据条数
	 * @param countable 是否计算数据总量，当数据量很大时，置为 false 可以一定程度提高效率
	 * @return 分页对象
	 */
	public Page<Map<String, Object>> queryForPage(String sql, List<Object> args, int pageIndex, int pageLimit, boolean countable) {
		if (ValidateCommons.isEmpty(args)) {
			args = new ArrayList<Object>();
		}
		String pagingSql = this.dialectCommons.SQL.buildPagingSql(sql, pageIndex, pageLimit);
		Object[] argArray = args.toArray();

		int rowTotal = getRowTotal(sql, args, countable);
		List<Map<String, Object>> results = this.jdbcTemplate.queryForList(pagingSql, argArray);
		return new Page<Map<String, Object>>(pageIndex, pageLimit, rowTotal, results);
	}

	/**
	 * <pre>
	 * 分页查询
	 * </pre>
	 * @param sql
	 * @param args 绑定参数，可以为空
	 * @param resultType 结果类型
	 * @param pageIndex 页数
	 * @param pageLimit 每页数据条数
	 * @return
	 */
	public <T> Page<T> queryForPage(String sql, List<Object> args, Class<T> resultType, int pageIndex, int pageLimit) {
		return queryForPage(sql, args, resultType, pageIndex, pageLimit, true);
	}

	/**
	 * <pre>
	 * 分页查询
	 * </pre>
	 * @param sql
	 * @param args 绑定参数，可以为空
	 * @param resultType 结果类型
	 * @param pageIndex 页数
	 * @param pageLimit 每页数据条数
	 * @param countable 是否计算数据总量，当数据量很大时，置为 false 可以一定程度提高效率
	 * @return
	 */
	public <T> Page<T> queryForPage(String sql, List<Object> args, Class<T> resultType, int pageIndex, int pageLimit, boolean countable) {
		if (ValidateCommons.isEmpty(args)) {
			args = new ArrayList<Object>();
		}
		String pagingSql = this.dialectCommons.SQL.buildPagingSql(sql, pageIndex, pageLimit);
		Object[] argArray = args.toArray();

		int rowTotal = getRowTotal(sql, args, countable);
		List<T> results = this.jdbcTemplate.queryForList(pagingSql, argArray, resultType);
		return new Page<T>(pageIndex, pageLimit, rowTotal, results);
	}

	/**
	 * <pre>
	 * 计数查询
	 * </pre>
	 * @param sql
	 * @param args 绑定参数，可以为空
	 * @return 计数
	 */
	public int queryForCount(String sql, List<Object> args) {
		if (ValidateCommons.isEmpty(args)) {
			args = new ArrayList<Object>();
		}
		Object[] argArray = args.toArray();

		String countSql = this.dialectCommons.SQL.buildCountSql(sql);
		Integer rowTotal = this.jdbcTemplate.queryForObject(countSql, argArray, Integer.class);
		return rowTotal;
	}

	/**
	 * <pre>
	 * 清空表数据
	 * </pre>
	 * @param tableName 表名
	 */
	public void executeClearTable(String tableName) {
		String clearTableSql = this.dialectCommons.SQL.buildClearTableSql(tableName);
		this.jdbcTemplate.execute(clearTableSql);
	}

	/**
	 * <pre>
	 * 执行数据拷贝，从 from 表拷贝 fromColumns 列到 to 表 toColumns 列
	 * </pre>
	 * @param from
	 * @param fromColumns 默认为 *
	 * @param to
	 * @param toColumns 默认为空，当不为空时默认在生成的字符串头尾添加括号
	 * @param whereFragment where 条件片段，如果条件判断无 where 关键字，默认在头部添加 where 关键字
	 * @param args 绑定参数，可以为空
	 * @return 拷贝数据计数
	 */
	public int executeCopyData(String from, List<String> fromColumns, String to, List<String> toColumns, String whereFragment, List<Object> args) {
		if (ValidateCommons.isEmpty(args)) {
			args = new ArrayList<Object>();
		}
		Object[] argArray = args.toArray();

		String copyDataSql = this.dialectCommons.SQL.buildCopyDataSql(from, fromColumns, to, toColumns, whereFragment);
		return this.jdbcTemplate.update(copyDataSql, argArray);
	}

	private int getRowTotal(String sql, List<Object> args, boolean countable) {
		// 默认为一个接近无限大数字
		int rowTotal = 100000000;
		if (countable) {
			rowTotal = queryForCount(sql, args);
		}
		return rowTotal;
	}

	/**
	 * <pre>
	 * 基于 jdbc 连接参数获取数据源
	 * </pre>
	 * @param driver jdbc 驱动
	 * @param url jdbc URL
	 * @param username jdbc 用户
	 * @param password jdbc 密码
	 * @return 数据源
	 * @throws Exception
	 */
	public static DataSource getDataSource(String driver, String url, String username, String password) 
			throws Exception {
		Properties properties = new Properties();
		properties.setProperty(DruidDataSourceFactory.PROP_DRIVERCLASSNAME, driver);
		properties.setProperty(DruidDataSourceFactory.PROP_URL, url);
		properties.setProperty(DruidDataSourceFactory.PROP_USERNAME, username);
		properties.setProperty(DruidDataSourceFactory.PROP_PASSWORD, password);
		return DruidDataSourceFactory.createDataSource(properties);
	}

	/**
	 * <pre>
	 * 基于 jndi 获取数据源
	 * </pre>
	 * @param jndiName jndi 名称
	 * @return 数据源
	 * @throws Exception
	 */
	public static DataSource getDataSource(String jndiName) 
			throws Exception {
		JndiObjectFactoryBean jndiObjectFactoryBean = new JndiObjectFactoryBean();
		jndiObjectFactoryBean.setJndiName(jndiName);
		try {
			jndiObjectFactoryBean.afterPropertiesSet();
		} catch (IllegalArgumentException e) {
			Logger.warn(JdbcCommons.class, "基于 jndi 获取数据源失败", e);
			jndiObjectFactoryBean.setResourceRef(true);
			jndiObjectFactoryBean.afterPropertiesSet();
		} catch (NamingException e) {
			Logger.warn(JdbcCommons.class, "基于 jndi 获取数据源失败", e);
			jndiObjectFactoryBean.setResourceRef(true);
			jndiObjectFactoryBean.afterPropertiesSet();
		}
		return (DataSource) jndiObjectFactoryBean.getObject();
	}

	/**
	 * <pre>
	 * 打开数据库连接
	 * </pre>
	 * @param driver jdbc 驱动
	 * @param url jdbc URL
	 * @param username jdbc 用户
	 * @param password jdbc 密码
	 * @return 数据库连接
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static Connection open(String driver, String url, String username, String password) 
			throws ClassNotFoundException, SQLException  {
		Properties properties =new Properties();
		properties.setProperty("user", username);
		properties.setProperty("password", password);
		properties.setProperty("remarks", "true"); // 某些数据库（Oracle、Mysql）需要设置此属性才行抓取表备注信息

		try {
			BeanCommons.getClass(driver);
		} catch (Exception e) {
			throw new ClassNotFoundException("加载 jdbc 驱动异常", e);
		}
		Connection conn = DriverManager.getConnection(url, properties);
		conn.setAutoCommit(true);
		return conn;
	}

	/**
	 * <pre>
	 * 打开数据库连接
	 * </pre>
	 * @param dataSource 数据源
	 * @return 数据库连接
	 * @throws SQLException
	 */
	public static Connection open(DataSource dataSource) throws SQLException {
		return dataSource.getConnection();
	}

	/**
	 * <pre>
	 * 关闭 ResultSet、Statement、Connection 三类对象
	 * 系统自动判断对象类型
	 * </pre>
	 * @param obj ResultSet、Statement、Connection 三类对象
	 * @throws SQLException
	 */
	public static void close(Object obj) 
			throws SQLException {
		if (ValidateCommons.isEmpty(obj)) {
			return;
		}
		if (obj instanceof ResultSet) {
			((ResultSet) obj).close();
		} else if (obj instanceof Statement) {
			((Statement) obj).close();
		} else if (obj instanceof Connection) {
			Connection conn = (Connection) obj;
			if (!conn.isClosed()) {
				conn.close();
			}
		}
	}

	/**
	 * <pre>
	 * 依次关闭 ResultSet、Statement、Connection
	 * </pre>
	 * @param conn
	 * @param st
	 * @param rs
	 * @throws SQLException
	 */
	public static void close(Connection conn, Statement st, ResultSet rs) 
			throws SQLException {
		try {
			close(rs);
		} finally {
			try {
				close(st);
			} finally {
				close(conn);
			}
		}
	}

	/**
	 * <pre>
	 * 构建 sql where 条件
	 * </pre>
	 * @param pazkage jdbc 查询参数包
	 * @return sql where 条件
	 */
	public static SqlPackage buildSqlWhere(JdbcQueryPackage pazkage) {
		StringBuffer sql = new StringBuffer();
		List<Object> args = new ArrayList<Object>();
		if (ValidateCommons.isNotEmpty(pazkage)) {
			ArrayListValuedHashMap<String, String> storer = new ArrayListValuedHashMap<String, String>();
			List<JdbcQueryPackage> packages = pazkage.getPackages();
			List<JdbcQueryParam> params = pazkage.getParams();
			if (ValidateCommons.isNotEmpty(packages)) {
				for (JdbcQueryPackage jqp : packages) {
					SqlPackage sqlPackage = buildSqlWhere(jqp);
					String packageName = jqp.getPackageName();
					storer.put(packageName, sqlPackage.getSql());
					args.addAll(sqlPackage.getArgs());
				}
			}
			if (ValidateCommons.isNotEmpty(params)) {
				for (JdbcQueryParam jqp : params) {
					String value = jqp.getValue();
					String groupName = jqp.getGroupName();
					String fragment = jqp.getFragment();
					storer.put(groupName, fragment);
					if (JdbcQueryParam.BIND_MODE_NORMATIVE.equals(jqp.getBindMode())) {
						args.add(value);
					}
				}
			}

			if (storer.size() > 0) {
				sql.append("(");
				int groupIndex = 0;
				Iterator<Entry<String, Collection<String>>> it = storer.asMap().entrySet().iterator();
				while(it.hasNext()) {
					Entry<String, Collection<String>> entry = it.next();
					Collection<String> fragments = entry.getValue();
					if (ValidateCommons.isNotEmpty(fragments)) {
						int fragmentIndex = 0;
						if (groupIndex > 0) {
							sql.append(" or ");
						}
						sql.append("(");
						for (String fragment : fragments) {
							if (fragmentIndex > 0) {
								sql.append(" and ");
							}
							sql.append(fragment);
							fragmentIndex++;
						}
						sql.append(")");
						groupIndex++;
					}
				}
				sql.append(")");
			}
		}
		return new SqlPackage(sql.toString(), args);
	}

	/**
	 * <pre>
	 * 基于 ResultSet 构建 Map
	 * </pre>
	 * @param rs ResultSet
	 * @return Map
	 * @throws SQLException
	 */
	public static Map<String, Object> buildObject(ResultSet rs) throws SQLException {
		return buildObject(rs, rs.getMetaData());
	}

	/**
	 * <pre>
	 * 基于 ResultSet 构建 Map
	 * </pre>
	 * @param rs ResultSet
	 * @param rsmd ResultSetMetaData
	 * @return
	 * @throws SQLException
	 */
	public static Map<String, Object> buildObject(ResultSet rs, ResultSetMetaData rsmd) throws SQLException {
		Map<String, Object> result = new HashMap<String, Object>();
		for (int i = 1; i <= rsmd.getColumnCount(); i++) {
			/*
			 * getColumnName 返回的是 field 的原始名称
			 * getColumnLabel 返回的是 field 的别名
			 */
			String label = rsmd.getColumnLabel(i);
			Object value = rs.getObject(label);
			result.put(label, value);
		}
		return result;
	}

	/**
	 * <pre>
	 * 基于 ResultSet 构建对象
	 * 使用 ResultSetMapping 注解为 Class 属性映射数据库字段
	 * </pre>
	 * @param rs ResultSet
	 * @param clazz 对象 class
	 * @return 对象
	 * @throws SQLException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws SecurityException
	 * @throws IllegalArgumentException
	 * @throws NoSuchMethodException
	 * @throws InvocationTargetException
	 */
	public static Object buildObject(ResultSet rs, Class<?> clazz) 
			throws SQLException, InstantiationException, IllegalAccessException, 
			SecurityException, IllegalArgumentException, NoSuchMethodException, 
			InvocationTargetException {
		Map<String, String> fieldMapping = new HashMap<String, String>();
		Field[] attributes = clazz.getDeclaredFields();
		for (Field attribute : attributes) {
			String attrName = attribute.getName();
			if (attribute.isAnnotationPresent(JdbcResultSetMapping.class)) {
				JdbcResultSetMapping rsm = attribute.getAnnotation(JdbcResultSetMapping.class);
				if (ValidateCommons.isNotEmpty(rsm)) {
					fieldMapping.put(rsm.value(), attrName);
				}
			}
		}
		return buildObject(rs, fieldMapping, clazz);
	}

	/**
	 * <pre>
	 * 基于 ResultSet 构建对象
	 * </pre>
	 * @param rs ResultSet
	 * @param fieldMapping 数据库字段映射，Map<数据库字段, 对象属性>
	 * @param clazz 对象 class
	 * @return 对象
	 * @throws SQLException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws SecurityException
	 * @throws IllegalArgumentException
	 * @throws NoSuchMethodException
	 * @throws InvocationTargetException
	 */
	public static Object buildObject(ResultSet rs, Map<String, String> fieldMapping, Class<?> clazz) 
			throws SQLException, InstantiationException, IllegalAccessException, 
			SecurityException, IllegalArgumentException, NoSuchMethodException, 
			InvocationTargetException {
		Map<String, Object> map = new HashMap<String, Object>();
		Map<String, Object> result = buildObject(rs);
		for (String field : result.keySet()) {
			String attrName = fieldMapping.get(field);
			if (ValidateCommons.isNotEmpty(attrName)) {
				map.put(attrName, result.get(field));
			}
		}
		return BeanCommons.toBean(map, clazz);
	}

}
